package org.compiere.process;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import java.util.TreeSet;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;

import jxl.Workbook;
import jxl.format.Border;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

//import jxl.Range;
//import jxl.Workbook;
//import jxl.format.Alignment;
//import jxl.format.Border;
//import jxl.format.BorderLineStyle;
//import jxl.format.CellFormat;
//import jxl.format.Colour;
//import jxl.format.Format;
//import jxl.format.VerticalAlignment;
//import jxl.write.Label;
//import jxl.write.Number;
//import jxl.write.NumberFormats;
//import jxl.write.WritableCellFormat;
//import jxl.write.WritableFont;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBCSACRInputFormAppend2Ext;
import org.compiere.model.MBCSACRInputFormAppend3_1;
import org.compiere.model.MBCSACRInputFormAppend3_1Ext;
import org.compiere.model.MBCSACRInputFormAppend4;
import org.compiere.model.MBCSACRInputFormCompany;
import org.compiere.model.MBCSFRInputForm;
import org.compiere.model.MBCSFRInputFormColumn;
import org.compiere.model.MBCSFRInputFormCompany;
import org.compiere.model.MBCSFRInputFormLine;
import org.compiere.model.MBCSFRInputFormPeriod;
import org.compiere.model.MBPartner;
import org.compiere.model.MBPartnerLocation;
import org.compiere.model.MLocation;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;
import org.utils.*;

public class BCS_MaterialityLevel extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private BigDecimal PeriodFrom = null;
	private MPeriod mperiod = null;
	private BigDecimal PeriodTo = null;
	private MPeriod mperiodTo = null;
	private BigDecimal Year = null;
	private BigDecimal Version = null;
	
	private int iYear = 0;
	private BigDecimal C_BPartner = null;

	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/** */
	private String path = "";

	private int AD_Org_ID = -1;
	private int AD_Client_ID = -1;
	
	HSSFWorkbook wb = null;
	String Mother = "";
	String Address = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			
			else if (name.equals("C_BPartner_ID")) {
				C_BPartner = (BigDecimal) para[i].getParameter();
			}
			
			else if (name.equals("C_Year_ID")) {
				Year = (BigDecimal) para[i].getParameter();
				iYear = Integer.parseInt(para[i].getInfo());
			}
			
			else if (name.equals("PeriodFrom")) {
				PeriodFrom = (BigDecimal) para[i].getParameter();
			}
			
			else if (name.equals("PeriodTo")) {
				PeriodTo = (BigDecimal) para[i].getParameter();
			}
			else
				
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		// AD_Client_ID = getAD_Client_ID();
		// AD_Org_ID = getAD_Org_ID();

		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {

		MPeriod p = new MPeriod(Env.getCtx(), PeriodFrom.intValue(), null);
		MPeriod p2 = new MPeriod(Env.getCtx(), PeriodTo.intValue(), null);
		Date d1 = p.getStartDate();
		Date d2 = p2.getEndDate();
		
		List<line> list7 = getDatas(d1, d2, "7");
		for(int i = 0; i<list7.size(); i++)
			if(list7.get(i).amt.compareTo(Env.ZERO) == 0)
				list7.remove(i);
		
		List<line> list6 = getDatas(d1, d2, "6");
		for(int i = 0; i<list6.size(); i++)
			if(list6.get(i).amt.compareTo(Env.ZERO) == 0)
				list6.remove(i);
		
		if (list7.size() == 0 && list6.size() == 0)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");

		
		// ---------------------------------------------------------------
		// Write the output to a file
		
		String file = "BCS_MaterialityLevel_tmpl.xlsx";
		String filePath = Util.localFilePath + file;
//		String file = Util.localFilePath +"test.xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));
		

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		
		//--------------------------------------------------------------------------
		//let's do it
		
//
		MBPartner bp = new MBPartner(Env.getCtx(), C_BPartner.intValue(), null);
		String bpname = bp.getName(); 

		mperiod = new MPeriod(Env.getCtx(), PeriodFrom.intValue(), null);
		mperiodTo = new MPeriod(Env.getCtx(), PeriodTo.intValue(), null);
		String period = "";
		period = "Период: " + org.utils.DataUtils.getMonthNameRUIP(mperiod.getStartDate()) + " " +(mperiod.getStartDate().getYear()+1900)+"г. - " +
				org.utils.DataUtils.getMonthNameRUIP(mperiodTo.getEndDate()) + " " +(mperiod.getEndDate().getYear()+1900)+"г.";
		
		Calendar c1 = Calendar.getInstance();
		c1.setTime(d1);
		Calendar c2 = Calendar.getInstance();
		c2.setTime(d2);
		
		Row R;
		Cell C;
		//write caption----------------------
		R = sheet.getRow(2);
		if(R!=null){
			C = R.getCell(0);
			if(C==null)
				C = R.createCell(0);
			C.setCellValue(bpname);
		}
		
		R = sheet.getRow(3);
		if(R!=null){
			C = R.getCell(0);
			if(C==null)
				C = R.createCell(0);
			C.setCellValue(period);
		}
		
		
		
		
		// create header----------------------------------------
		int countOfMonth = c2.get(c2.MONTH) - c1.get(c1.MONTH);
		for(int i=0; i <= countOfMonth; i++){
			R = sheet.getRow(5);
			if(R!=null){
				C = R.getCell(i+3);
				if(C==null)
					C = R.createCell(i+3);
				
				Cell C2 = R.getCell(3);
				if(C2 != null)
					C.setCellStyle(C2.getCellStyle());
				
				Calendar c = Calendar.getInstance();
				c.setTime(d1);
				c.set(Calendar.MONTH, c1.get(c1.MONTH)+i);
				
				C.setCellValue(org.utils.DataUtils.getMonthNameRUIP(c.getTime()));
				sheet.setColumnWidth(i+3, 5000);
			}
			
			R = sheet.getRow(6);
			if(R!=null){
				C = R.getCell(i+3);
				if(C==null)
					C = R.createCell(i+3);
				
				Cell C2 = R.getCell(3);
				if(C2 != null)
					C.setCellStyle(C2.getCellStyle());
			}

			
		}
			
		//shift 7-----------------------------------------
		Set<String> list = new TreeSet<String>();
		
		for(line l: list7)
			list.add(l.account);
		
		if((list.size()-1) > 0)
			sheet.shiftRows(7, 14, list.size()-1);
		
		//set styles 7------------------------------------
		for(int i=0; i<list.size(); i++){
			R = sheet.getRow(6);
			Row R2 = sheet.getRow(7+i);
			for(int j=0; j<(4+countOfMonth); j++){
				if(R2==null)
					R2 = sheet.createRow(7+i);	
					C = R2.getCell(j);
					if(C==null)
						C = R2.createCell(j);
					
					Cell C2 = R.getCell(j);
					if(C2 != null)
						C.setCellStyle(C2.getCellStyle());
			}
		}
		
		//write datas 7-----------------------------------
		Iterator<String> it = list.iterator();
//		int i = 0;
		int row = 6;
		while(it.hasNext()){
			
			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.getCell(0);
			if(C == null)
				C = R.createCell(0);
			String v = it.next();
			C.setCellValue(v);
			
			String name = "";
			for(int j=0; j < list7.size(); j++){
				if(v.equals(list7.get(j).account)){
					name = list7.get(j).name;
					break;
				}
			}

			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			
			C.setCellValue(name);
			//write datas months---------------
			
			BigDecimal rowTotal = Env.ZERO;
			int column = 3;
			for(int m=0; m <= countOfMonth; m++){
				
				
				Calendar c = Calendar.getInstance();
				c.setTime(d1);
				c.add(Calendar.MONTH, m);
				Date d = c.getTime();
				BigDecimal value = Env.ZERO;
				
				for(int r=0; r<list7.size(); r++){
					
//					System.out.println("list7.get(r).dateacct.getMonth(): " + list7.get(r).dateacct.getMonth() + ",	c.get(Calendar.MONTH): " + c.get(Calendar.MONTH));
					
					if(list7.get(r).dateacct.getMonth() == c.get(Calendar.MONTH) && list7.get(r).account.equals(v))
						value = value.add(list7.get(r).amt);
				}
				
				R = sheet.getRow(row);
				if(R == null)
					R = sheet.createRow(row);
				
				C = R.getCell(column);
				if(C == null)
					C = R.createCell(column);
				
				C.setCellValue(value.doubleValue());
				
				column++;
				rowTotal = rowTotal.add(value);
				
			}
			
//			System.out.println("------------------");
			// write 7 total columns
			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.getCell(2);
			if(C == null)
				C = R.createCell(2);
			
			C.setCellValue(rowTotal.doubleValue());
			
			row++;
//			i++;
		}
		
		// set formula 7
		R = sheet.getRow(row);
		if(R == null)
			R = sheet.createRow(row);
			
			int column = 2;
			for(int m=0; m <= countOfMonth+1; m++){
				Cell C2 = R.getCell(column + m);
				if(C2 == null)
					C2 = R.createCell(column + m);
				
				C2.setCellFormula("SUM(" + getLetter(3+m) + 7 + ":" + getLetter(3+m) + (7 + (list.size()-1))+")");
//				System.out.println("SUM(" + getLetter(3+m) + 7 + ":" + getLetter(3+m) + (7 + (list.size()-1))+")");
			}
			
			
			
			
			
		
		
		
		//---------------------------------------
		//             6 
		
		// create header----------------------------------------
//		int countOfMonth = c2.get(c2.MONTH) - c1.get(c1.MONTH);
		for(int i=0; i <= countOfMonth; i++){
			
			R = sheet.getRow(8+list.size());
			if(R!=null){
				C = R.getCell(i+3);
				if(C==null)
					C = R.createCell(i+3);
				
				Cell C2 = R.getCell(3);
				if(C2 != null)
					C.setCellStyle(C2.getCellStyle());
			}

			
		}
			
		//shift 6-----------------------------------------
		Set<String>list2 = new TreeSet<String>();
		
		for(line l: list6)
			list2.add(l.account);
		
		
		//System.out.println(8+list7.size()+","+ 14+list7.size() +","+  list.size());
		if((list2.size()-1) > 0)
			sheet.shiftRows(8+list.size(), 14+list.size(), list2.size()-1);
		
		//set styles 6------------------------------------
		for(int i=0; i<=list2.size(); i++){
			R = sheet.getRow(6);
			Row R2 = sheet.getRow(7+list.size()+i);
			for(int j=0; j<(4+countOfMonth); j++){
				if(R2==null)
					R2 = sheet.createRow(7+list.size()+i);	
					C = R2.getCell(j);
					if(C==null)
						C = R2.createCell(j);
					
					Cell C2 = R.getCell(j);
					if(C2 != null)
						C.setCellStyle(C2.getCellStyle());
			}
		}
		
		//write datas 6-----------------------------------
		it = list2.iterator();
		int i = 0;
		row = 7+list.size();
		while(it.hasNext()){
			
			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.getCell(0);
			if(C == null)
				C = R.createCell(0);
			String v = it.next();
			C.setCellValue(v);
			
			String name = "";
			for(int j=0; j < list6.size(); j++){
				if(v.equals(list6.get(j).account)){
					name = list6.get(j).name;
					break;
				}
			}

			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			
			C.setCellValue(name);
			//write datas months---------------
			
			BigDecimal rowTotal = Env.ZERO;
			column = 3;
			for(int m=0; m <= countOfMonth; m++){
				
				
				Calendar c = Calendar.getInstance();
				c.setTime(d1);
				c.add(Calendar.MONTH, m);
				Date d = c.getTime();
				BigDecimal value = Env.ZERO;
				
				for(int r=0; r<list6.size(); r++){
					if(list6.get(r).dateacct.getMonth() == c.get(Calendar.MONTH) && list6.get(r).account.equals(v))
						value = value.add(list6.get(r).amt);
				}
				
				R = sheet.getRow(row);
				if(R == null)
					R = sheet.createRow(row);
				
				C = R.getCell(column);
				if(C == null)
					C = R.createCell(column);
				
				C.setCellValue(value.doubleValue());
				
				column++;
				rowTotal = rowTotal.add(value);
				
			}
			// write 6 total columns
			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.getCell(2);
			if(C == null)
				C = R.createCell(2);
			
			C.setCellValue(rowTotal.doubleValue());
			
			row++;
			i++;
		}		
		
		// set formula 6
		R = sheet.getRow(row);
		if(R == null)
			R = sheet.createRow(row);
			
			column = 2;
			for(int m=0; m <= countOfMonth+1; m++){
				Cell C2 = R.getCell(column + m);
				if(C2 == null)
					C2 = R.createCell(column + m);
				
				C2.setCellFormula("SUM(" + getLetter(3+m) + (7 + list.size()+1) + ":" + getLetter(3+m) + (7 + list.size()+1 + list2.size()-1)+")");
			}
		
		row++;	
		
		
		
		//--------------------------------footer -----------------	
		// Base value
			R = sheet.getRow(row);
			C = R.getCell(3);
			for(int j=0; j <= countOfMonth; j++){
				int col = j+3;
				Cell C2 = R.getCell(col);
				if(C2 == null)
					C2 = R.createCell(col);
					
				C2.setCellStyle(C.getCellStyle());
				C2.setCellFormula(getLetter(col+1) + (6+list.size()+list2.size()+2)+ "-" + getLetter(col+1)+(6+list.size()+1));
				
			}
			
		row++;
		
		// levels
		level l = getLevel(C_BPartner.intValue());
		// general level
		R = sheet.getRow(row);
		C = R.getCell(3);
		for(int j=0; j <= countOfMonth+1; j++){
			int col = j+2;
			Cell C2 = R.getCell(col);
			if(C2 == null)
				C2 = R.createCell(col);
				
			C2.setCellStyle(C.getCellStyle());
			C2.setCellFormula(getLetter(col+1)+row+	"*"	+ l.general+"%");
			
		}
		
		row++;	
		
		//personal level
		R = sheet.getRow(row);
		C = R.getCell(3);
		for(int j=0; j <= countOfMonth+1; j++){
			int col = j+2;
			Cell C2 = R.getCell(col);
			if(C2 == null)
				C2 = R.createCell(col);
				
			C2.setCellStyle(C.getCellStyle());
			C2.setCellFormula(getLetter(col+1)+row+	"*"	+ l.indiv+"%");
			
		}
		
		row++;	
	
		

		//--------------------------------------------------------------------------
		
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");
	}

	protected List<line> getDatas(Date d1, Date d2, String acct){
		List<line> result = new ArrayList<line>();
		
		String sql = 
		"select ce.value, fa.dateacct, ce.name, coalesce(sum(fa.amtacctdr + fa.amtacctcr),0)\n" +
				"	from adempiere.fact_acct as fa \n" +
				"	inner join adempiere.c_elementvalue as ce on fa.account_id = ce.c_elementvalue_id\n" +
				"	\n" +
				"	and fa.dateacct between '"+d1+"'::date and '"+d2+"'::date\n" +
				"	and ce.value like '"+acct+"%'\n" +
				"	group by ce.value, fa.dateacct, ce.name "
		;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
		
		while(rs.next()) {
			line l = new line();
				l.account = rs.getString(1);
				l.dateacct = rs.getDate(2);
				l.name = rs.getString(3);
				l.amt = rs.getBigDecimal(4);
			result.add(l);
			
		}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, this.toString(), e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		
		return result;
	}
	
	protected level getLevel(int c_BPartner_ID){
		level result = new level();
		
		String sql = 
				"	select bcs_generalmaterialitylevel, bcs_indivmaterialitylevel from adempiere.bcs_materialityleveldict	"+ 
				"	where c_bpartner_id = " + c_BPartner_ID	
				;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
		
		while(rs.next()) {
			result.general = rs.getInt(1);
			result.indiv = rs.getInt(2);
		}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, this.toString(), e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		
		return result;
	}	
	
	
	protected String excNull(Object o){
		String result = "";
			if(o != null && o instanceof String)
				result = (String) o;
			
		return result;
	}

	class level{
		int general = 0;
		int indiv = 0;
	}
	
	class line{
		String account = "";
		Date dateacct = null;
		String name = "";
		BigDecimal amt = Env.ZERO;
	}
	
	protected String getLetter(int column){
		String result = "";
		column--;
		switch(column){
		case 0:
			result = "A";
			break;
		case 1:
			result = "B";
			break;
		case 2:
			result = "C";
			break;
		case 3:
			result = "D";
			break;
		case 4:
			result = "E";
			break;
		case 5:
			result = "F";
			break;
		case 6:
			result = "G";
			break;
		case 7:
			result = "G";
			break;
		case 8:
			result = "H";
			break;
		case 9:
			result = "I";
			break;
		case 10:
			result = "J";
			break;
		case 11:
			result = "K";
			break;
		case 12:
			result = "L";
			break;
		case 13:
			result = "M";
			break;
		default: result = "";
			break;
		}
		return result;
	}
	
	
}

